package com.restphp.java.utils.mysql.builder.core;

import com.restphp.java.utils.mysql.builder.po.NativePo;
import com.restphp.java.utils.mysql.builder.po.OrderPo;
import com.restphp.java.utils.mysql.builder.enums.OrderEnum;
import com.restphp.java.utils.string.core.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by sofical on 2020/7/3.
 */
public class SqlBuilder {
    private String table;
    private String output = " * ";
    private String[] group = new String[]{};
    private List<OrderPo> order = new ArrayList<>();
    private Integer[] limit = new Integer[]{};
    private SqlConditionUtils condition = new SqlConditionUtils();
    private SqlValuesUtils values = new SqlValuesUtils();

    /**
     * 设置查询的表名.
     * @param table 表名.
     * @return SQL拼接器.
     */
    public SqlBuilder setTable(String table) {
        this.table = table;
        return this;
    }

    /**
     * 获取查询的表名.
     * @return 表名.
     */
    public String getTable() {
        return this.table;
    }

    /**
     * 设置需要查询的输出.
     * @param output 需要查询的输出.
     * @return SQL拼接器.
     */
    public SqlBuilder setOutput(String output) {
        this.output = output;
        return this;
    }

    /**
     * 获取需要查询的输出.
     * @return 需要查询的输出.
     */
    public String getOutput() {
        return this.output;
    }

    /**
     * 设置查询条件拼接器.
     * @param condition 查询条件拼接器.
     * @return SQL拼接器.
     */
    public SqlBuilder setCondition(SqlConditionUtils condition) {
        this.condition = condition;
        return this;
    }

    /**
     * 获取查询条件拼接器.
     * @return 查询条件拼接器.
     */
    public SqlConditionUtils getCondition() {
        return this.condition;
    }

    /***
     * 设置变更操作值.
     * @param values 变更操作值.
     * @return 查询条件拼接器.
     */
    public SqlBuilder setValues(SqlValuesUtils values) {
        this.values = values;
        return this;
    }

    /**
     * 获取变更操作值.
     * @return 变更操作值.
     */
    public SqlValuesUtils getValues() {
        return this.values;
    }

    /**
     * 设置group条件.
     * @param group group条件.
     * @return SQL拼接器.
     */
    public SqlBuilder setGroup(String[] group) {
        this.group = group;
        return this;
    }

    /**
     * 获取group条件.
     * @return group条件.
     */
    public String[] getGroup() {
        return this.group;
    }

    /**
     * 设置查询范围.
     * @param limit 查询的条数.
     * @return SQL拼接器.
     */
    public SqlBuilder setLimit(Integer limit) {
        Integer[] limitInfo = new Integer[] {limit};
        this.limit = limitInfo;
        return this;
    }

    /**
     * 设置查询范围.
     * @param from 查询的起启位置.
     * @param limit 查询的条数.
     * @return SQL拼接器.
     */
    public SqlBuilder setLimit(Integer from, Integer limit) {
        Integer[] limitInfo = new Integer[] {from, limit};
        this.limit = limitInfo;
        return this;
    }

    /**
     * 设置查询范围.
     * @param page 起启页.
     * @param limit 查询的条数.
     * @return SQL拼接器.
     */
    public SqlBuilder setPage(Integer page, Integer limit) {
        if (page < 1) {
            page = 1;
        }
        Integer from = (page - 1) * limit;
        Integer[] limitInfo = new Integer[] {from, limit};
        this.limit = limitInfo;
        return this;
    }

    /**
     * 获取查询范围.
     * @return 查询范围.
     */
    public Integer[] getLimit() {
        return this.limit;
    }

    /**
     * 设置排序条件.
     * @param column 排序列.
     * @param order 排序方式.
     * @return SQL拼接器.
     */
    public SqlBuilder setOrder(String column, OrderEnum order) {
        OrderPo orderDo = new OrderPo();
        orderDo.setColumn(column);
        orderDo.setOrder(order);
        this.order.add(orderDo);
        return this;
    }

    /**
     * 获取排序条件.
     * @return 排序条件.
     */
    public List<OrderPo> getOrder() {
        return this.order;
    }

    /**
     * 获取查询拼接结果.
     * @return SQL拼接结果对象.
     */
    public NativePo getSelect() {
        StringBuilder sql = new StringBuilder("select ");
        List<Object> values = new ArrayList<>();
        //拼接输出
        sql.append(this.getOutput()).append(" from ");
        //拼接查询表
        sql.append(this.getTable());
        //拼接查询条件
        NativePo nativeDo = this.getCondition().getWhere();
        if (!StringUtils.isBlank(nativeDo.getSql())) {
            sql.append(" where ").append(nativeDo.getSql());
            values.addAll(nativeDo.getValues());
        }
        //拼接group条件
        sql.append(this.getGroupResult());
        //拼接order
        sql.append(this.getOrderResult());
        //拼接查询范围
        sql.append(this.getLimitResult());

        NativePo result = new NativePo();
        result.setSql(sql.toString());
        result.setValues(values);
        return result;
    }

    /**
     * 获取替换插入拼接结果
     * @return SQL拼接结果对象.
     */
    public NativePo getReplace() {
        return this.getInsertPo(true);
    }

    /**
     * 获取插入拼接结果.
     * @return SQL拼接结果对象.
     */
    public NativePo getInsert() {
        return this.getInsertPo(false);
    }

    /**
     * 获取插入拼接结果.
     * @return SQL拼接结果对象.
     */
    private NativePo getInsertPo(Boolean replace) {
        StringBuilder sql = new StringBuilder(replace ? "replace" : "insert").append(" into ");
        //拼接表
        sql.append(this.getTable()).append(" ");
        //接接列和值
        NativePo valueOperation = this.getValues().getInsert();
        sql.append(valueOperation.getSql());

        NativePo insert = new NativePo();
        insert.setSql(sql.toString());
        insert.setValues(valueOperation.getValues());
        return insert;
    }

    /**
     * 获取更新SQL语句拼接结果.
     * @return SQL拼接结果对象.
     */
    public NativePo getUpdate() {
        StringBuilder sql = new StringBuilder(" update ");
        List<Object> values = new ArrayList<>();
        //拼接表
        sql.append(this.getTable()).append(" set ");
        //拼接值
        NativePo valueOperation = this.getValues().getUpdate();
        sql.append(valueOperation.getSql());
        values.addAll(valueOperation.getValues());
        //拼接条件
        NativePo whereOperation = this.getCondition().getWhere();
        if (!StringUtils.isBlank(whereOperation.getSql())) {
            sql.append(" where ").append(whereOperation.getSql());
            values.addAll(whereOperation.getValues());
        }
        //返回结果
        NativePo result = new NativePo();
        result.setSql(sql.toString());
        result.setValues(values);
        return result;
    }

    /**
     * 获取删除SQL语句拼接结果.
     * @return SQL拼接结果对象.
     */
    public NativePo getDelete() {
        StringBuilder sql = new StringBuilder(" delete from ");
        List<Object> values = new ArrayList<>();
        //拼接表
        sql.append(this.getTable());
        //拼接条件
        NativePo whereOperation = this.getCondition().getWhere();
        if (!StringUtils.isBlank(whereOperation.getSql())) {
            sql.append(" where ").append(whereOperation.getSql());
            values.addAll(whereOperation.getValues());
        }
        //返回结果
        NativePo result = new NativePo();
        result.setSql(sql.toString());
        result.setValues(values);
        return result;
    }

    /**
     * 获取拼接group条件结果.
     * @return 拼接group条件结果.
     */
    private String getGroupResult() {
        String group = "";
        if (this.getGroup().length > 0) {
            for (String item : this.getGroup()) {
                group += ("".equals(group) ? "" : ", ") + item;
            }
            group = " group by " + group;
        }
        return group;
    }

    /**
     * 获取order拼接结果.
     * @return order拼接结果.
     */
    private String getOrderResult() {
        String order = "";
        if (!this.getOrder().isEmpty()) {
            for (OrderPo orderDo : this.getOrder()) {
                order += ("".endsWith(order) ? "" : ", ") + orderDo.getColumn() + " " + orderDo.getOrder().getOrder();
            }
            order = " order by " + order;
        }
        return order;
    }

    /**
     * 获取查询范围拼接结果.
     * @return 查询范围拼接结果.
     */
    private String getLimitResult() {
        String limit = "";
        if (this.getLimit().length == 1) {
            limit = " limit " + String.valueOf(this.getLimit()[0]);
        } else if (this.getLimit().length == 2) {
            limit = " limit " + String.valueOf(this.getLimit()[0]) + ", " + String.valueOf(this.getLimit()[1]);
        }
        return limit;
    }
}
